/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     2013/3/19 9:26:49                            */
/*==============================================================*/


IF EXISTS (SELECT 1
   FROM SYS.SYSREFERENCES R JOIN SYS.SYSOBJECTS O ON (O.ID = R.CONSTID AND O.TYPE = 'F')
   WHERE R.FKEYID = OBJECT_ID('UR_REPORTPARAMS') AND O.NAME = 'FK_UR_REPOR_REF_UR_REPOR')
ALTER TABLE UR_REPORTPARAMS
   DROP CONSTRAINT FK_UR_REPOR_REF_UR_REPOR
go

IF EXISTS (SELECT 1
   FROM SYS.SYSREFERENCES R JOIN SYS.SYSOBJECTS O ON (O.ID = R.CONSTID AND O.TYPE = 'F')
   WHERE R.FKEYID = OBJECT_ID('BS_DATASET_PARAMETER') AND O.NAME = 'FK_PARAMETER__REF_SQL')
ALTER TABLE BS_DATASET_PARAMETER
   DROP CONSTRAINT FK_PARAMETER__REF_SQL
go

IF EXISTS (SELECT 1
   FROM SYS.SYSREFERENCES R JOIN SYS.SYSOBJECTS O ON (O.ID = R.CONSTID AND O.TYPE = 'F')
   WHERE R.FKEYID = OBJECT_ID('BS_DATASET_SQL') AND O.NAME = 'FK_SQL__REF_CLASS')
ALTER TABLE BS_DATASET_SQL
   DROP CONSTRAINT FK_SQL__REF_CLASS
go

IF EXISTS (SELECT 1
   FROM SYS.SYSREFERENCES R JOIN SYS.SYSOBJECTS O ON (O.ID = R.CONSTID AND O.TYPE = 'F')
   WHERE R.FKEYID = OBJECT_ID('SYS_MAILOWNER') AND O.NAME = 'FK_SYS_MAIL_FK__SYS_MAIL')
ALTER TABLE SYS_MAILOWNER
   DROP CONSTRAINT FK_SYS_MAIL_FK__SYS_MAIL
go

IF EXISTS (SELECT 1
   FROM SYS.SYSREFERENCES R JOIN SYS.SYSOBJECTS O ON (O.ID = R.CONSTID AND O.TYPE = 'F')
   WHERE R.FKEYID = OBJECT_ID('SYS_DEPT_ROLES') AND O.NAME = 'FK_SYS_DEPT_REF_SYS_DEPT')
ALTER TABLE SYS_DEPT_ROLES
   DROP CONSTRAINT FK_SYS_DEPT_REF_SYS_DEPT
go

IF EXISTS (SELECT 1
   FROM SYS.SYSREFERENCES R JOIN SYS.SYSOBJECTS O ON (O.ID = R.CONSTID AND O.TYPE = 'F')
   WHERE R.FKEYID = OBJECT_ID('SYS_FUNCTION_TABLE') AND O.NAME = 'FK_SYS_FUNC_REF_SYS_FUNC')
ALTER TABLE SYS_FUNCTION_TABLE
   DROP CONSTRAINT FK_SYS_FUNC_REF_SYS_FUNC
go

IF EXISTS (SELECT 1
   FROM SYS.SYSREFERENCES R JOIN SYS.SYSOBJECTS O ON (O.ID = R.CONSTID AND O.TYPE = 'F')
   WHERE R.FKEYID = OBJECT_ID('SYS_MENU') AND O.NAME = 'FK_SYS_MENU_FK__SYS_SUBS')
ALTER TABLE SYS_MENU
   DROP CONSTRAINT FK_SYS_MENU_FK__SYS_SUBS
go

IF EXISTS (SELECT 1
   FROM SYS.SYSREFERENCES R JOIN SYS.SYSOBJECTS O ON (O.ID = R.CONSTID AND O.TYPE = 'F')
   WHERE R.FKEYID = OBJECT_ID('SYS_OPERATOR_ROLE') AND O.NAME = 'FK_SYS_OPER_REF_SYS_DEPT')
ALTER TABLE SYS_OPERATOR_ROLE
   DROP CONSTRAINT FK_SYS_OPER_REF_SYS_DEPT
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_AUTO_AWAKE')
            AND   TYPE = 'U')
   DROP TABLE SYS_AUTO_AWAKE
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_DEPT')
            AND   TYPE = 'U')
   DROP TABLE SYS_DEPT
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_USER')
            AND   TYPE = 'U')
   DROP TABLE SYS_USER
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_OPERATOR_AWAKE')
            AND   TYPE = 'U')
   DROP TABLE SYS_OPERATOR_AWAKE
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_SHORTCUT')
            AND   TYPE = 'U')
   DROP TABLE SYS_SHORTCUT
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('UR_REPORTTYPE')
            AND   TYPE = 'U')
   DROP TABLE UR_REPORTTYPE
go

IF EXISTS (SELECT 1
            FROM  SYSINDEXES
           WHERE  ID    = OBJECT_ID('UR_REPORTPARAMS')
            AND   NAME  = 'UR_PARAMETER_NAME'
            AND   INDID > 0
            AND   INDID < 255)
   DROP INDEX UR_REPORTPARAMS.UR_PARAMETER_NAME
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('UR_REPORTPARAMS')
            AND   TYPE = 'U')
   DROP TABLE UR_REPORTPARAMS
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('UR_REPORTTEMPLATE')
            AND   TYPE = 'U')
   DROP TABLE UR_REPORTTEMPLATE
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('BS_AREA')
            AND   TYPE = 'U')
   DROP TABLE BS_AREA
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('BS_DATASET_CLASS')
            AND   TYPE = 'U')
   DROP TABLE BS_DATASET_CLASS
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('BS_DATASET_PARAMETER')
            AND   TYPE = 'U')
   DROP TABLE BS_DATASET_PARAMETER
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('BS_DATASET_SQL')
            AND   TYPE = 'U')
   DROP TABLE BS_DATASET_SQL
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('BS_DROPDOWN')
            AND   TYPE = 'U')
   DROP TABLE BS_DROPDOWN
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_MAILINFO')
            AND   TYPE = 'U')
   DROP TABLE SYS_MAILINFO
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_MAILOWNER')
            AND   TYPE = 'U')
   DROP TABLE SYS_MAILOWNER
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_NOWORKDAY')
            AND   TYPE = 'U')
   DROP TABLE SYS_NOWORKDAY
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_DEPT_ROLES')
            AND   TYPE = 'U')
   DROP TABLE SYS_DEPT_ROLES
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_FUNCTION')
            AND   TYPE = 'U')
   DROP TABLE SYS_FUNCTION
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_FUNCTION_TABLE')
            AND   TYPE = 'U')
   DROP TABLE SYS_FUNCTION_TABLE
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_MENU')
            AND   TYPE = 'U')
   DROP TABLE SYS_MENU
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_NEWID_SEED')
            AND   TYPE = 'U')
   DROP TABLE SYS_NEWID_SEED
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_OPERATOR_ROLE')
            AND   TYPE = 'U')
   DROP TABLE SYS_OPERATOR_ROLE
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_ROLE_FUNCTION')
            AND   TYPE = 'U')
   DROP TABLE SYS_ROLE_FUNCTION
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_ROLES')
            AND   TYPE = 'U')
   DROP TABLE SYS_ROLES
go

IF EXISTS (SELECT 1
            FROM  SYSOBJECTS
           WHERE  ID = OBJECT_ID('SYS_SUBSYSTEM')
            AND   TYPE = 'U')
   DROP TABLE SYS_SUBSYSTEM
go

/*==============================================================*/
/* Table: SYS_AUTO_AWAKE                                        */
/*==============================================================*/
CREATE TABLE SYS_AUTO_AWAKE (
   AWAKE_ID             INTEGER              NOT NULL,
   AWAKE_NAME           VARCHAR(40)          NULL,
   AWAKE_TYPE           INTEGER              NULL,
   SQL_STR              VARCHAR(5000)        NULL,
   AWAKE_WORD           VARCHAR(200)         NULL,
   GRADING              INTEGER              NULL,
   URL_ADDRESS          VARCHAR(255)         NULL,
   NOTE                 VARCHAR(200)         NULL,
   CONSTRAINT PK_SYS_AUTO_AWAKE PRIMARY KEY (AWAKE_ID)
)
go

/*==============================================================*/
/* Table: SYS_DEPT                                              */
/*==============================================================*/
CREATE TABLE SYS_DEPT (
   DEPT_ID              CHAR(20)             NOT NULL,
   DEPT_NO              VARCHAR(20)          NULL,
   DEPT_NAME            VARCHAR(20)          NULL,
   PARENT_ID            CHAR(20)             NOT NULL DEFAULT '0',
   DEPT_TYPE            VARCHAR(20)          NULL,
   IS_VIRTUAL           SMALLINT             NULL,
   MANAGER_ID           VARCHAR(80)          NULL,
   MANAGER_NAME         VARCHAR(160)         NULL,
   DEPT_KIND            INT                  NULL DEFAULT 0,
   INNERDEPT_NO         VARCHAR(20)          NULL,
   SYSTEM_STR           VARCHAR(200)         NULL,
   DEL_FLAG             SMALLINT             NOT NULL DEFAULT 0,
   FOR_SUPPLY           CHAR(20)             NULL,
   IS_SUPPLY            SMALLINT             NULL DEFAULT 1,
   CONSTRAINT PK_SYS_DEPT PRIMARY KEY NONCLUSTERED (DEPT_ID)
)
go

/*==============================================================*/
/* Table: SYS_USER                                              */
/*==============================================================*/
CREATE TABLE SYS_USER (
   USER_ID              VARCHAR(20)          NOT NULL,
   DEPT_ID              CHAR(20)             NOT NULL,
   IS_MANAGER           SMALLINT             NOT NULL DEFAULT 0,
   USER_NAME            VARCHAR(20)          NOT NULL,
   USER_PASSWORD        VARCHAR(200)         NULL,
   CONTACT_PHONE        VARCHAR(40)          NULL,
   INNER_PHONE          VARCHAR(20)          NULL,
   OFFICE_PHONE         VARCHAR(40)          NULL,
   HOME_PHONE           VARCHAR(40)          NULL,
   E_MAIL               VARCHAR(80)          NULL,
   ADDRESS              VARCHAR(80)          NULL,
   DISPLAY_ORDER        INT                  NULL,
   REMARK               VARCHAR(200)         NULL,
   DERATE_LIMIT         NUMERIC(14,2)        NULL,
   USER_TYPE            INT                  NULL,
   CREATE_OPERATOR_ID   VARCHAR(10)          NULL,
   SUB_SYSTEM           INT                  NULL DEFAULT 1,
   OTHER_USER1          VARCHAR(20)          NULL,
   OTHER_PWD1           VARCHAR(20)          NULL,
   OTHER_USER2          VARCHAR(20)          NULL,
   OTHER_PWD2           VARCHAR(20)          NULL,
   OTHER_USER3          VARCHAR(20)          NULL,
   OTHER_PWD3           VARCHAR(20)          NULL,
   DEL_FLAG             SMALLINT             NOT NULL DEFAULT 0,
   CONSTRAINT PK_SYS_USER PRIMARY KEY NONCLUSTERED (USER_ID)
)
go

/*==============================================================*/
/* Table: SYS_OPERATOR_AWAKE                                    */
/*==============================================================*/
CREATE TABLE SYS_OPERATOR_AWAKE (
   AWAKE_ID             INTEGER              NOT NULL,
   IS_USE               BIT                  NULL,
   USER_TYPE            INT                  NOT NULL,
   USER_ID              VARCHAR(20)          NOT NULL,
   CONSTRAINT PK_SYS_OPERATOR_AWAKE PRIMARY KEY (AWAKE_ID, USER_TYPE, USER_ID)
)
go

/*==============================================================*/
/* Table: SYS_SHORTCUT                                          */
/*==============================================================*/
CREATE TABLE SYS_SHORTCUT (
   USER_ID              VARCHAR(20)          NOT NULL,
   MENU_NO              VARCHAR(20)          NOT NULL,
   MENU_NAME            VARCHAR(100)         NOT NULL,
   CONSTRAINT PK_SYS_SHORTCUT PRIMARY KEY NONCLUSTERED (USER_ID, MENU_NO)
)
go

/*==============================================================*/
/* Table: UR_REPORTTYPE                                         */
/*==============================================================*/
CREATE TABLE UR_REPORTTYPE (
   REPORTTYPE_ID        INT                  NOT NULL,
   REPORTTYPE_NAME      VARCHAR(40)          NULL,
   SUB_SYSTEM           SMALLINT             NOT NULL,
   REMARK               VARCHAR(200)         NULL,
   CONSTRAINT PK_UR_REPORTTYPE PRIMARY KEY NONCLUSTERED (REPORTTYPE_ID)
)
go

/*==============================================================*/
/* Table: UR_REPORTTEMPLATE                                     */
/*==============================================================*/
CREATE TABLE UR_REPORTTEMPLATE (
   TEMPLATE_ID          INT                  NOT NULL,
   REPORTTYPE_ID        INT                  NOT NULL,
   TEMPLATE_NAME        VARCHAR(30)          NULL,
   FILE_CONTENT         VARCHAR(400)         NULL,
   TEMPLATE_FILE        VARCHAR(80)          NULL,
   TEMPLATE_TYPE        INT                  NOT NULL DEFAULT 1,
   REPLACE_OPTION       VARCHAR(400)         NULL,
   USE_RANGE            VARCHAR(400)         NULL DEFAULT '0',
   TEMPLATE             IMAGE                NULL,
   SUB_SYSTEM           SMALLINT             NULL,
   CLASS1               VARCHAR(80)          NULL,
   CLASS2               VARCHAR(80)          NULL,
   CLASS3               VARCHAR(80)          NULL,
   REMARK               VARCHAR(200)         NULL,
   CONSTRAINT PK_UR_REPORTTEMPLATE PRIMARY KEY NONCLUSTERED (TEMPLATE_ID)
)
go

/*==============================================================*/
/* Table: UR_REPORTPARAMS                                       */
/*==============================================================*/
CREATE TABLE UR_REPORTPARAMS (
   PARAM_ID             INT                  NOT NULL,
   TEMPLATE_ID          INT                  NOT NULL,
   PARAM_NAME           VARCHAR(400)         NULL,
   T_PARAM_NAME         VARCHAR(2000)        NOT NULL,
   PARAMETER_TYPE       INT                  NULL DEFAULT 0,
   DATASET_ID           INT                  NULL,
   FIELD_NAME           VARCHAR(200)         NULL,
   DATAROW              INT                  NOT NULL DEFAULT 0,
   FIELD_TYPE           VARCHAR(40)          NULL DEFAULT '0',
   PARAM_DEFAULT        VARCHAR(400)         NULL DEFAULT '0',
   IS_CHANGED           INT                  NOT NULL DEFAULT 1,
   REPLACE_LEN          INT                  NULL DEFAULT 0,
   ALIGN_MODE           INT                  NULL DEFAULT 0,
   REPEAT_TYPE          INT                  NULL DEFAULT 0,
   FIX_REPRAT_NUM       INT                  NULL DEFAULT 0,
   REPEAT_NUM           INT                  NULL DEFAULT 0,
   OUT_OPERATE          INT                  NULL DEFAULT 1,
   IS_MERGE             INT                  NOT NULL DEFAULT 0,
   REMARK               VARCHAR(100)         NULL,
   CONSTRAINT PK_UR_REPORTPARAMS PRIMARY KEY NONCLUSTERED (PARAM_ID),
   CONSTRAINT FK_UR_REPOR_REF_UR_REPOR FOREIGN KEY (TEMPLATE_ID)
      REFERENCES UR_REPORTTEMPLATE (TEMPLATE_ID)
)
go

/*==============================================================*/
/* Index: UR_PARAMETER_NAME                                     */
/*==============================================================*/
CREATE INDEX UR_PARAMETER_NAME ON UR_REPORTPARAMS (
PARAM_NAME ASC
)
go

/*==============================================================*/
/* Table: BS_AREA                                               */
/*==============================================================*/
CREATE TABLE BS_AREA (
   AREA_ID              INT                  NOT NULL,
   AREA_NO              CHAR(8)              NULL,
   AREA_NAME            VARCHAR(20)          NULL,
   PARENT_ID            INT                  NULL,
   LEVEL_NAME           VARCHAR(10)          NULL,
   DEL_FLAG             INT                  NULL,
   CONSTRAINT PK_BS_AREA PRIMARY KEY (AREA_ID)
)
go

/*==============================================================*/
/* Table: BS_DATASET_CLASS                                      */
/*==============================================================*/
CREATE TABLE BS_DATASET_CLASS (
   DATASET_CLASS        INT                  NOT NULL,
   SUB_SYSTEM           SMALLINT             NOT NULL,
   CLASS_NAME           VARCHAR(40)          NULL,
   CLASS_DESCR          VARCHAR(80)          NULL,
   MODIFY_FLAG          SMALLINT             NOT NULL DEFAULT 0,
   CONSTRAINT PK_BS_DATASET_CLASS PRIMARY KEY NONCLUSTERED (DATASET_CLASS)
)
go

/*==============================================================*/
/* Table: BS_DATASET_SQL                                        */
/*==============================================================*/
CREATE TABLE BS_DATASET_SQL (
   DATASET_ID           INT                  NOT NULL,
   DEPT_ID              CHAR(20)             NOT NULL,
   DATASET_CLASS        INT                  NULL,
   DATASET_NAME         VARCHAR(80)          NULL,
   DATASET_SQL          VARCHAR(4000)        NULL,
   HAS_PARAMETER        SMALLINT             NOT NULL DEFAULT 0,
   ALLOW_MODIFY         SMALLINT             NOT NULL DEFAULT 0,
   DATASET_MODE         INT                  NULL,
   DATASET_USE          VARCHAR(200)         NULL,
   IS_VISIBLE           SMALLINT             NOT NULL DEFAULT 1,
   CONSTRAINT PK_BS_DATASET_SQL PRIMARY KEY NONCLUSTERED (DATASET_ID),
   CONSTRAINT FK_SQL__REF_CLASS FOREIGN KEY (DATASET_CLASS)
      REFERENCES BS_DATASET_CLASS (DATASET_CLASS)
)
go

/*==============================================================*/
/* Table: BS_DATASET_PARAMETER                                  */
/*==============================================================*/
CREATE TABLE BS_DATASET_PARAMETER (
   PARAMETER_ID         VARCHAR(80)          NOT NULL,
   PARAMETER_NAME       VARCHAR(80)          NULL,
   DATASET_ID           INT                  NOT NULL,
   NOTES                VARCHAR(400)         NULL,
   IS_SYSPARAMETER      SMALLINT             NOT NULL DEFAULT 0,
   SELECT_TYPE          INT                  NULL,
   DATA_SOURCE          INT                  NULL,
   DATA_CONTENT         VARCHAR(2000)        NULL,
   PARAMETER_DEFAULT    VARCHAR(400)         NULL,
   CONSTRAINT PK_BS_DATASET_PARAMETER PRIMARY KEY NONCLUSTERED (PARAMETER_ID, DATASET_ID),
   CONSTRAINT FK_PARAMETER__REF_SQL FOREIGN KEY (DATASET_ID)
      REFERENCES BS_DATASET_SQL (DATASET_ID)
)
go

/*==============================================================*/
/* Table: BS_DROPDOWN                                           */
/*==============================================================*/
CREATE TABLE BS_DROPDOWN (
   DROPDOWN_NAME        VARCHAR(40)          NOT NULL,
   SELECT_TYPE          INT                  NULL,
   DATA_SOURCE          INT                  NULL,
   DATA_CONTENT         VARCHAR(2000)        NULL,
   DATA_TYPE            INT                  NOT NULL DEFAULT 1,
   REMARK               VARCHAR(200)         NULL,
   CONSTRAINT PK_BS_DROPDOWN PRIMARY KEY NONCLUSTERED (DROPDOWN_NAME)
)
go

/*==============================================================*/
/* Table: SYS_MAILINFO                                          */
/*==============================================================*/
CREATE TABLE SYS_MAILINFO (
   MAILID               INT                  NOT NULL,
   ADDRESSEEIDLIST      VARCHAR(1000)        NULL,
   ADDRESSEENAMELIST    VARCHAR(1000)        NULL,
   MAKECOPYIDLIST       VARCHAR(1000)        NULL,
   MAKECOPYNAMELIST     VARCHAR(1000)        NULL,
   ACCESSORIES          VARCHAR(1000)        NULL,
   TITLE                VARCHAR(800)         NULL,
   CONTENT              VARCHAR(4000)        NULL,
   SENDTIME             DATETIME             NOT NULL,
   ATTACH_FLAG          INT                  NULL,
   EXIGENCEFLAG         INT                  NULL,
   READFLAG             INT                  NULL DEFAULT 1,
   S_MAILID             INT                  NULL,
   SENDUSERID           VARCHAR(20)          NULL,
   SENDUSERNAME         VARCHAR(20)          NULL,
   CONSTRAINT PK_SYS_MAILINFO PRIMARY KEY NONCLUSTERED (MAILID)
)
go

/*==============================================================*/
/* Table: SYS_MAILOWNER                                         */
/*==============================================================*/
CREATE TABLE SYS_MAILOWNER (
   MAILID               INT                  NOT NULL,
   USER_ID              VARCHAR(20)          NOT NULL,
   FOLDERFLAG           INT                  NOT NULL,
   READFLAG             INT                  NULL DEFAULT 0,
   DELETEFLAG           INT                  NULL DEFAULT 0,
   CONSTRAINT PK_SYS_MAILOWNER PRIMARY KEY NONCLUSTERED (MAILID, USER_ID, FOLDERFLAG),
   CONSTRAINT FK_SYS_MAIL_FK__SYS_MAIL FOREIGN KEY (MAILID)
      REFERENCES SYS_MAILINFO (MAILID)
)
go

/*==============================================================*/
/* Table: SYS_NOWORKDAY                                         */
/*==============================================================*/
CREATE TABLE SYS_NOWORKDAY (
   HOLIDAY              DATETIME             NOT NULL,
   HOLIDAY_YEAR         INT                  NULL,
   HOLIDAY_MONTH        INT                  NULL,
   HOLIDAY_DAY          INT                  NULL,
   HOLIDAY_NAME         VARCHAR(50)          NULL,
   HOLIDAY_TYPE         VARCHAR(20)          NULL,
   REMARK               VARCHAR(100)         NULL,
   CONSTRAINT PK_SYS_NOWORKDAY PRIMARY KEY (HOLIDAY)
)
go

/*==============================================================*/
/* Table: SYS_ROLES                                             */
/*==============================================================*/
CREATE TABLE SYS_ROLES (
   ROLE_ID              VARCHAR(10)          NOT NULL,
   ROLE_NAME            VARCHAR(20)          NULL,
   CREATOR              VARCHAR(20)          NULL,
   DEPT_ID              CHAR(20)             NOT NULL,
   NOTES                VARCHAR(40)          NULL,
   DEL_FLAG             SMALLINT             NOT NULL DEFAULT 0,
   SUB_SYSTEM           INT                  NULL,
   CONSTRAINT PK_SYS_ROLES PRIMARY KEY NONCLUSTERED (ROLE_ID)
)
go

/*==============================================================*/
/* Table: SYS_DEPT_ROLES                                        */
/*==============================================================*/
CREATE TABLE SYS_DEPT_ROLES (
   MASTER_ID            INT                  NOT NULL,
   ROLE_ID              VARCHAR(10)          NOT NULL,
   DEPT_ID              CHAR(20)             NOT NULL,
   NOTES                VARCHAR(40)          NULL,
   CONSTRAINT PK_SYS_DEPT_ROLES PRIMARY KEY NONCLUSTERED (MASTER_ID),
   CONSTRAINT FK_SYS_DEPT_REF_SYS_DEPT FOREIGN KEY (DEPT_ID)
      REFERENCES SYS_DEPT (DEPT_ID),
   CONSTRAINT FK_SYS_DEPT_REF_SYS_ROLE FOREIGN KEY (ROLE_ID)
      REFERENCES SYS_ROLES (ROLE_ID)
)
go

/*==============================================================*/
/* Table: SYS_FUNCTION                                          */
/*==============================================================*/
CREATE TABLE SYS_FUNCTION (
   FUNCTION_ID          VARCHAR(20)          NOT NULL,
   FUNCTION_NAME        VARCHAR(40)          NULL,
   SUB_SYSTEM           SMALLINT             NOT NULL,
   FUNCTION_CLASS1      VARCHAR(40)          NULL,
   FUNCTION_CLASS2      VARCHAR(40)          NULL,
   EXEC_TYPE            INT                  NOT NULL DEFAULT 1,
   URL_ADDRESS          VARCHAR(400)         NULL,
   ENTRY_PARAMETER      VARCHAR(200)         NULL,
   PARAMETER2           VARCHAR(200)         NULL,
   PARAMETER3           VARCHAR(200)         NULL,
   USE_AREA             VARCHAR(800)         NULL,
   SYS_MEMO             VARCHAR(800)         NULL,
   HELP_POSITION        VARCHAR(200)         NULL,
   CONSTRAINT PK_SYS_FUNCTION PRIMARY KEY NONCLUSTERED (FUNCTION_ID)
)
go

/*==============================================================*/
/* Table: SYS_FUNCTION_TABLE                                    */
/*==============================================================*/
CREATE TABLE SYS_FUNCTION_TABLE (
   SYS_TABLE_KEY        INT                  NOT NULL,
   FUNCTION_ID          VARCHAR(20)          NOT NULL,
   TABLE_NAME           VARCHAR(80)          NULL,
   TABLE_SCRIPT         VARCHAR(80)          NULL,
   PRIVILEGE_FIELD      VARCHAR(80)          NULL,
   PRIVILEGE_USE        SMALLINT             NULL,
   SYSTEM_STR           VARCHAR(200)         NULL,
   REMARK               VARCHAR(200)         NULL,
   CONSTRAINT PK_SYS_FUNCTION_TABLE PRIMARY KEY NONCLUSTERED (SYS_TABLE_KEY),
   CONSTRAINT FK_SYS_FUNC_REF_SYS_FUNC FOREIGN KEY (FUNCTION_ID)
      REFERENCES SYS_FUNCTION (FUNCTION_ID)
)
go

/*==============================================================*/
/* Table: SYS_SUBSYSTEM                                         */
/*==============================================================*/
CREATE TABLE SYS_SUBSYSTEM (
   SUB_SYSTEM           SMALLINT             NOT NULL,
   SYSTEM_NAME          VARCHAR(20)          NULL,
   SYSTEM_STR           VARCHAR(200)         NULL,
   REMARK               VARCHAR(200)         NULL,
   CONSTRAINT PK_SYS_SUBSYSTEM PRIMARY KEY NONCLUSTERED (SUB_SYSTEM)
)
go

/*==============================================================*/
/* Table: SYS_MENU                                              */
/*==============================================================*/
CREATE TABLE SYS_MENU (
   MENU_NO              VARCHAR(20)          NOT NULL,
   MENU_NAME            VARCHAR(100)         NOT NULL,
   FUNCTION_ID          VARCHAR(20)          NULL,
   IS_LEAF              SMALLINT             NOT NULL DEFAULT 1,
   PARENT_MENU_NO       VARCHAR(20)          NULL,
   ICONURL              VARCHAR(100)         NULL,
   SUB_SYSTEM           SMALLINT             NOT NULL,
   EXEC_TYPE            INT                  NULL DEFAULT 1,
   URL_ADDRESS          VARCHAR(400)         NULL,
   ENTRY_PARAMETER      VARCHAR(200)         NULL,
   MENU_PARAMETER1      VARCHAR(200)         NULL,
   MENU_PARAMETER2      VARCHAR(200)         NULL,
   MENU_PARAMETER3      VARCHAR(200)         NULL,
   MENU_LEVEL           INT                  NULL,
   INNER_NO             VARCHAR(20)          NULL,
   MENU_ORDER           INT                  NULL,
   CONSTRAINT PK_SYS_MENU PRIMARY KEY NONCLUSTERED (MENU_NO),
   CONSTRAINT FK_SYS_MENU_FK__SYS_SUBS FOREIGN KEY (SUB_SYSTEM)
      REFERENCES SYS_SUBSYSTEM (SUB_SYSTEM)
)
go

/*==============================================================*/
/* Table: SYS_NEWID_SEED                                        */
/*==============================================================*/
CREATE TABLE SYS_NEWID_SEED (
   TABLENAME            VARCHAR(80)          NOT NULL,
   NEW_ID               INT                  NULL,
   ACTUAL_TABLE         VARCHAR(80)          NULL,
   ACTUAL_FIELD         VARCHAR(80)          NULL,
   PARAMETER            VARCHAR(80)          NULL,
   REMARK               VARCHAR(200)         NULL,
   CONSTRAINT PK_SYS_NEWID_SEED PRIMARY KEY NONCLUSTERED (TABLENAME)
)
go

/*==============================================================*/
/* Table: SYS_OPERATOR_ROLE                                     */
/*==============================================================*/
CREATE TABLE SYS_OPERATOR_ROLE (
   USER_ID              VARCHAR(20)          NOT NULL,
   MASTER_ID            INT                  NOT NULL,
   NOTES                VARCHAR(40)          NULL,
   CONSTRAINT PK_SYS_OPERATOR_ROLE PRIMARY KEY NONCLUSTERED (USER_ID, MASTER_ID),
   CONSTRAINT FK_SYS_OPER_REF_SYS_USER FOREIGN KEY (USER_ID)
      REFERENCES SYS_USER (USER_ID),
   CONSTRAINT FK_SYS_OPER_REF_SYS_DEPT FOREIGN KEY (MASTER_ID)
      REFERENCES SYS_DEPT_ROLES (MASTER_ID)
)
go

/*==============================================================*/
/* Table: SYS_ROLE_FUNCTION                                     */
/*==============================================================*/
CREATE TABLE SYS_ROLE_FUNCTION (
   ROLE_ID              VARCHAR(10)          NOT NULL,
   MENU_NO              VARCHAR(20)          NOT NULL,
   READONLY             SMALLINT             NOT NULL DEFAULT 0,
   CONSTRAINT PK_SYS_ROLE_FUNCTION PRIMARY KEY NONCLUSTERED (ROLE_ID, MENU_NO),
   CONSTRAINT FK_SYS_ROLE_REF_ROLE_FUNCTION FOREIGN KEY (ROLE_ID)
      REFERENCES SYS_ROLES (ROLE_ID)
)
go

